Databases in Mathematica Workshop

Chris Williamson and Joshua Martell

Wolfram Research, Inc.

DatabaseLink and Import

Import

Import understands MDB and DBF formats and gives us direct access to table data from a database file.  More about this later...

DatabaseLink

DatabaseLink provides a way to access a live database using querys.  Powerful, but more complicated.

Import

MDB (Microsoft Access 2000)

DBF (dBASE, Foxpro, etc.)

Import

Table Names

mdbfile = "C:\\Documents and Settings\\chrisw\\Desktop\\MondrianFoodMart.mdb";

Import[mdbfile, {"Elements"}]

Take[Sort@Import[mdbfile, {"Datasets"}], 20]//TableForm

Import

Employee Table Fields

Import[mdbfile, {"Datasets", "employee", "Labels"}]//TableForm

employees  = Import[mdbfile, {"Datasets", "employee", "LabeledData"}];

Form of "LabeledData"

{"field1" -> {data1, data2, data3, data4 ...}, "field2" -> {data1, data2, data3, data4} ...}

Take["full_name" /. employees, 10]//TableForm

Import

Education vs Salary

education=MapThread[Rule, {{"Partial High School","High School Degree", "Partial College","Bachelors Degree", "Graduate Degree"}, Range[5]}];

pos = Transpose[{"education_level", "salary"} /. employees]/.education;

data = Split[Sort@pos, #1[[1]]===#2[[1]]&][[All, All, 2]];

ListPlot[{Mean[#]&/@data, Max[#]&/@data}, PlotRange->{0, All},  Joined->True, Filling->{{1->{Axis, Blue}}, {2->{{1}, Red}}}, PlotLabel->Style["Education vs. Salary (Max & Mean)", FontSize->20]]

Import

Easy access for simple operations

No setup required, just use the file.

Import Questions?

My email is joshuam@wolfram.com.

Import

Import Questions?

My email is joshuam@wolfram.com.

What is DatabaseLink?

DatabaseLink is a Mathematica toolkit that provides a set of tools allowing convenient integration of Mathematica with database management systems. DatabaseLink exposes data to the vast world of analysis and visualization tools available in Mathematica. This lets you use DatabaseLink to quickly develop or enhance any data-centric application by applying the world's foremost high-level analysis system to your data.Mauris auctor nunc ac nisl. Nunc non turpis a dolor porta tristique. Nam accumsan massa scelerisque sapien.

How does DatabaseLink work?

Implemented using JDBC, DatabaseLink can connect to most popular database systems, such as MySQL, Sybase, Oracle, Microsoft SQL Server, PostgreSQL, Informix, and many more.  JDBC is the Java API for connecting to databases.

What is DatabaseLink?

An interface for manipulating and querying databases.

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

Java independent

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

Java independent

Works cross-platform

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

Java independent

Works cross-platform

Supports many different database through JDBC drivers

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

Java independent

Works cross-platform

Supports many different database through JDBC drivers

Manipulate and query the database using SQL or Mathematica commands

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

Java independent

Works cross-platform

Supports many different database through JDBC drivers

Manipulate and query the database using SQL or Mathematica commands

Includes a lightweight database

How does DatabaseLink work?

Establish a connection to the server.

How does DatabaseLink work?

Establish a connection to the server.

Manipulate and/or query data in the database using the connection.

How does DatabaseLink work?

Establish a connection to the server.

Manipulate and/or query data in the database using the connection.

Close connection.

Establish a connection

A connection can be opened to an existing data source bying passing the data source's name into OpenSQLConnection.

<<DatabaseLink`

conn=OpenSQLConnection["demo"]

Data sources

Existing data source names can be found using DataSourceNames.

DataSourceNames[]

Create a new data source

If no parameters are passed to OpenSQLConnection, a GUI will present itself and allow the user to choose an existing data source or configure a new data source that is saved to disk.

conn1 = OpenSQLConnection[]

Establish a connection (con't)

JDBC may be passed to OpenSQLConnection as a parameter to specify a specific JDBC connection.  JDBC may contain the JDBC driver class name and URL used to connect to the database.  Options such as Username and Password may be passed to OpenSQLConnection as well.

conn2 = OpenSQLConnection[JDBC["org.hsqldb.jdbcDriver", "jdbc:hsqldb:/temp/test2"], Username->"sa"]

Establish a connection (con't)

To make things a bit easier to use, JDBC information may be stored and reused.  In this example, we can specify hsqldb rather than the JDBC driver class name and we can leave off the protocol from the URL.

conn3 = OpenSQLConnection[JDBC["hsqldb", ToFileName[{$UserBaseDirectory, "DatabaseResources", "Examples"}, "test"]], Username->"sa"]

JDBC Drivers

Existing JDBC driver names can be found using JDBCDriverNames and properties of these drivers can be found using JDBCDriver..

JDBCDriverNames[]

JDBCDrivers["hsqldb"]

Establish a connection (con't)

Passwords are stored in plain text for existing data sources.  The password may also be set to "$Prompt", so the user will be prompted for the password when trying to connect to data source.

conn4 = OpenSQLConnection[JDBC["hsqldb", ToFileName[{$UserBaseDirectory, "DatabaseResources", "Examples"}, "test"]], Username->"sa", Password->"$Prompt"]

Listing open SQLConnections

SQLConnections is used to list open connections to databases.

SQLConnections[]

Closing a connection

CloseSQLConnection is used to close an open connection to the database.  This prevents resources from being wasted on a connection not being used.

CloseSQLConnection[conn];
CloseSQLConnection /@ SQLConnections[];

Executing SQL

SQL commands may be executed using SQLExecute.

conn=OpenSQLConnection["publisher"];
SQLExecute[conn, "SELECT * FROM authors"]

Querying table information

SQLTableNames may be used to retrieve the names of tables in the database.

SQLTableNames[conn]

Querying table information (con't)

SQLTables may be used to retrieve the tables in the database.  A list of SQLTable expressions are returned.

SQLTables[conn]

Querying table information (con't)

SQLTableInformation may be used to retrieve more detailed information about tables in the database.

SQLTableInformation[conn, ShowColumnHeadings->True]//TableForm

Querying column information

SQLColumnNames may be used to retrieve the names of columns in the database.  The result is a paired list of table and column names.

SQLColumnNames[conn, "AUTHORS"]

Querying column information (con't)

SQLColumns may be used to retrieve the columns in the database.  A list of SQLColumn expressions are returned.

SQLColumns[conn, "AUTHORS"]

Querying column information (con't)

SQLColumnInformation may be used to retrieve more detailed information about columns in the database.

SQLColumnInformation[conn, "AUTHORS", ShowColumnHeadings->True]//TableForm

Querying data

SQLSelect is used retrieve data from a database.  The second parameter is used specify which table to retrieve data from.

SQLSelect[conn, "SALES"]

Querying data (con't)

The option ShowColumnHeadings may be used to retrieve the column names along with the data from a table.

SQLSelect[conn, "SALES", ShowColumnHeadings->True]//TableForm

Querying data (con't)

The third parameter of SQLSelect may be used to specify which columns of data to retrieve from a table.

SQLSelect[conn, "SALES", {"STOR_ID", "PONUM"}, ShowColumnHeadings->True]//TableForm

Querying data (con't)

The fourth parameter of SQLSelect may be used to specify a condition that limits the amount of data retrieved from a table.

SQLSelect[conn, "SALES", {"STOR_ID", "PONUM"}, SQLColumn["STOR_ID"] == 7896, ShowColumnHeadings->True]//TableForm

Creating a table

SQLCreateTable is used to create a table. The second parameter is used to specify the table name. The third parameter specifies a list SQLColumn expressions defining columns of the table.

SQLCreateTable[conn, "CUSTOMER", {
SQLColumn["CUST_ID", DataTypeName->"INT"], 
SQLColumn["CUST_FNAME", DataTypeName->"VARCHAR"], 
SQLColumn["CUST_LNAME", DataTypeName->"VARCHAR"], 
SQLColumn["CUST_EMAIL", DataTypeName->"VARCHAR"]
}];
SQLTableNames[conn]

Inserting data

SQLInsert is used to store data in a table.  The second parameter specifies is the name of the table to insert data into.  The third parameter specifies the column names to insert data into.  The fourth parameter specifies the values of the data to be inserted into the table.

SQLInsert[conn, "CUSTOMER", {"CUST_ID","CUST_FNAME","CUST_LNAME", "CUST_EMAIL"}, {1, "Chris", "Williamson", "chrisw@wolfram.com"}];
SQLSelect[conn, "CUSTOMER"]

Updating data

SQLUpdate is used to update the contents of a table.  The second parameter specifies the table that is being updated.  The third parameter specifies the column names of the data being updated.  The fourth parameter specifies the values of the data being updated.

SQLUpdate[conn, "CUSTOMER", {"CUST_FNAME"}, {"CHRISTOPHER"}, SQLColumn["CUST_ID"] ==1];
SQLSelect[conn, "CUSTOMER"]

Deleting data

SQLDelete is used to delete the contents of a table.  The second parameter specifies the table of the data being deleted.  A condition may be specified as the third parameter that limits the data deleted.  If a condition is not specified, all data in a table will be deleted.

SQLDelete[conn, "CUSTOMER"]

Dropping a table

SQLDropTable is used to remove a table from the database.  The second parameter specifies the table to drop from the database.

SQLDropTable[conn, "CUSTOMER"];
CloseSQLConnection[conn]

Conclusion

Questions.

My email is chrisw@wolfram.com.

Import questions to joshuam@wolfram.com